1、查看SQL SERVER數(shù)據(jù)庫(kù)的連接數(shù)

SELECT *

? FROM master.dbo.sysprocesses

where dbid=DB_ID('OnlineExam')

?OnlineExam是自己的數(shù)據(jù)庫(kù)名稱(chēng)

?2、查詢某個(gè)數(shù)據(jù)庫(kù)用戶的連接情況

sp_who 'sa'

?3、查看數(shù)據(jù)庫(kù)允許的最大連接

select @@MAX_CONNECTIONS

?4、查看數(shù)據(jù)庫(kù)自上次啟動(dòng)以來(lái)的連接次數(shù)

SELECT @@CONNECTIONS

?5、關(guān)閉連接

上面的查詢可以得到spid，根據(jù)spid，關(guān)閉進(jìn)程就可以了。

Kill 54

6、SQL SERVER 2008 刪除某個(gè)數(shù)據(jù)庫(kù)的所有連接進(jìn)程

declare @spid int ;

declare @ddlstring nvarchar(max);

declare @dbname varchar(200);

set @dbname='OnLineExam';

declare tmpcur cursor

for select distinct spid as spid fromsys.sysprocesses

where dbid=db_id(@dbname) ;

OPEN tmpcur;

fetch tmpcur into @spid ;

while (@@FETCH_STATUS=0)

?begin

?? set @ddlstring=N'Kill '+CONVERT(nvarchar,@spid) ;

?? execute sp_executesql @ddlstring ;

?? fetch tmpcur into @spid ;

?end ;

?

close tmpcur ;

deallocate tmpcur ;

?

?7、查詢當(dāng)前數(shù)據(jù)庫(kù)連接并清空所有的數(shù)據(jù)庫(kù)連接

查詢數(shù)據(jù)庫(kù)實(shí)例DBName的所有連接

select * from master.dbo.sysprocesses where dbid = DB_ID(‘DBName’)

清除指定實(shí)例DBName的所有連接

USE master
go

declare @programName nvarchar(200),
@spid nvarchar(20)

declare cDblogin cursor for
select cast(spid as varchar(20)) AS spid from master..sysprocesses where dbid=db_id(‘db_id_demo’)
open cDblogin
fetch next from cDblogin into @spid
while @@fetch_status=0
begin
–防止自己終止自己的進(jìn)程
–否則會(huì)報(bào)錯(cuò)不能用KILL 來(lái)終止您自己的進(jìn)程
IF @spid <> @@SPID
exec( ‘kill? ‘+@spid)
fetch next from cDblogin into @spid
end
close cDblogin
deallocate cDblogin